﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTEKGBData : BaseFunction
    {
        
        public JLTEKGBData()
            : base()
        {

        }
        public JLTEKGBData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetEKGB(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "WASTAMM";
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            string FKRS = dwABPOS["FKRS"].ToString().Trim();
            string FLFD = dwABPOS["FLFD"].ToString().Trim();
            string SUPP = dwABPOS["SUPP"].ToString().Trim();
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM ( "
                    + " SELECT DISTINCT A.EKKAUFKRS||'-'||TO_NUMBER(A.EKKAUFLFD) AS ORDERNO, "
                    + " TO_NUMBER(A.EKKLIEFNR) AS SUPP,A.EKKBDAT,TRIM(C.LSNAME2) AS SUPPNAME, "
                    + " TRIM(utl_raw.cast_to_varchar2(B.EKPART)) AS ARTICLE,TRIM(E.GSHANDLNR) AS ARTDES,B.EKPME1*0.01 AS QTY, "
                    + " SUM(D.GPEINBRUT*0.01)OVER(PARTITION BY B.EKPAUFKRS,B.EKPAUFLFD,B.EKPAUFPOS,D.GPEINDAT) as INQTY, "
                    + " D.GPEINDAT,B.EKPPRME*0.001 AS PRICE,TRIM(A.EKKKNTRNRL) AS SCHEDNO "
                    + " FROM EKKOPF A,EKPOS B,LIEFDAT C,GPDAT D,GSDAT E "
                    + " WHERE A.EKKMANDANT = B.EKPMANDANT AND A.EKKAUFKRS = B.EKPAUFKRS "
                    + " AND A.EKKAUFLFD = B.EKPAUFLFD AND A.EKKMANDANT = C.LSMANDANT "
                    + " AND A.EKKLIEFNR = C.LSLIEFNR AND B.EKPMANDANT = D.GPMANDANT(+) "
                    + " AND B.EKPAUFKRS = D.GPBESTKRS(+) AND B.EKPAUFLFD = D.GPBESTLFD(+) "
                    + " AND B.EKPAUFPOS = D.GPBESTPOS(+) AND B.EKPSA = 'PO' "
                    + " AND B.EKPMANDANT = E.GSMANDANT "
                    + " AND utl_raw.cast_to_varchar2(B.EKPART) = E.GSART "
                    + " AND utl_raw.cast_to_varchar2(B.EKPVAR) = E.GSVAR "
                    + " AND ( A.EKKAUFKRS = '" + FKRS + "' OR '" + FKRS + "'='AA' ) "
                    + " AND ( A.EKKAUFLFD = '" + FLFD + "' OR '" + FLFD + "'='000000' ) "
                    + " AND ( A.EKKLIEFNR = '" + SUPP + "' OR '" + SUPP + "'='00000000' ) "
                    + " AND A.EKKBDAT >=" + FROMDATE + " AND A.EKKBDAT <=" + ENDDATE + " ) "
                    + " WHERE ((GPEINDAT >=" + FROMDNDATE + "  AND GPEINDAT <=" + ENDDNDATE + ") OR " + FROMDNDATE + "=0) "
                    + " AND ARTICLE LIKE '%"+ARTICLE+"%' "
                    + " order by GPEINDAT,ORDERNO "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SUPPNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SUPPNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ARTDES"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ARTDES"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTEKGBData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetEKGB(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}